const express = require("express");
const path = require("path");
const fs = require("fs");

const cors = require("cors");
const mysql = require("mysql2");
const ExcelJS = require("exceljs");
const PDFDocument = require("pdfkit");
require("pdfkit-table"); // 给 doc 增加 table 支持
const CJK_FONT_PATH = path.join(
  __dirname,
  "assets/fonts/SourceHanSansCN-Regular.ttf"
);

const PORT = 5000;

// // 允许跨域
// app.use(cors());
// app.use(express.json({ limit: '20mb' }));
// app.use(express.urlencoded({ extended: true, limit: '20mb' }));

// 数据库配置
const dbConfig = {
  host: "localhost",
  user: "root",
  password: "12345678",
  database: "Test",
};

const pool = mysql.createPool({
  host: process.env.DB_HOST || "127.0.0.1",
  user: process.env.DB_USER || "root",
  password: process.env.DB_PASSWORD || "12345678",
  database: process.env.DB_DATABASE || "Test",
  port: Number(process.env.DB_PORT || 3306),
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 10000,
});
const promisePool = pool.promise();

// ---------- App ----------
const app = express();
app.use(cors({ origin: true, credentials: true }));
app.use(express.json({ limit: "5mb" }));
app.use(express.urlencoded({ extended: true, limit: "5mb" }));
app.use((req, res, next) => {
  // Long export can take a while; keep connection open
  res.setTimeout(120000, () => {
    console.warn("Response timeout:", req.method, req.originalUrl);
  });
  next();
});
app.get("/health", (req, res) => {
  res.status(200).json({ ok: true, time: Date.now() });
});

// ⚠️ 用 0.0.0.0，避免只绑定 127.0.0.1 导致容器/设备访问不到
app.listen(PORT, "0.0.0.0", () => {
  console.log(`API listening at http://0.0.0.0:${PORT}`);
});
// ==================== 辅助函数 ====================
// --- helpers: sanitize & csv-escape ---
function sanitizeVal(v) {
  if (v === null || v === undefined) return "";
  if (v instanceof Date) return v.toISOString().slice(0, 19).replace("T", " ");
  if (typeof v === "object") return JSON.stringify(v);
  return String(v);
}

function csvEscape(s) {
  const str = String(s ?? "");
  // 如果包含逗号/引号/换行，用引号包裹并双写内部引号
  return /[",\n]/.test(str) ? `"${str.replace(/"/g, '""')}"` : str;
}

// 根据表名分配图标
function getTableIcon(tableName) {
  const name = tableName.toLowerCase();
  if (
    name.includes("user") ||
    name.includes("patient") ||
    name.includes("患者")
  )
    return "👤";
  if (name.includes("doctor") || name.includes("医生")) return "👨‍⚕️";
  if (name.includes("order") || name.includes("处方")) return "💊";
  if (name.includes("department") || name.includes("科室")) return "🏥";
  if (name.includes("record") || name.includes("记录")) return "📋";
  return "📊";
}

// 构建JOIN语句
function buildJoinClause(relations) {
  if (!relations || relations.length === 0) return "";

  let joins = [];
  for (const rel of relations) {
    joins.push(
      `JOIN \`${rel.table2}\` ON \`${rel.table1}\`.\`${rel.field1}\` = \`${rel.table2}\`.\`${rel.field2}\``
    );
  }
  return joins.join(" ");
}

// 构建WHERE子句（简单模式）
function buildSimpleWhere(conditions) {
  if (!conditions || conditions.length === 0) return "";

  let clauses = [];
  for (let i = 0; i < conditions.length; i++) {
    const cond = conditions[i];
    if (!cond.table || !cond.field || !cond.value) continue;

    let clause;
    if (cond.operator === "LIKE") {
      clause = `\`${cond.table}\`.\`${cond.field}\` LIKE '%${mysql
        .escape(cond.value)
        .replace(/'/g, "")}%'`;
    } else {
      clause = `\`${cond.table}\`.\`${cond.field}\` ${
        cond.operator
      } ${mysql.escape(cond.value)}`;
    }

    if (i < conditions.length - 1 && cond.logic) {
      clause += ` ${cond.logic}`;
    }

    clauses.push(clause);
  }

  return clauses.length > 0 ? "WHERE " + clauses.join(" ") : "";
}

// 构建SELECT字段列表
function buildSelectFields(tables, availableFields) {
  let fields = [];
  for (const table of tables) {
    const tableFields = availableFields[table] || [];
    for (const field of tableFields) {
      fields.push(`\`${table}\`.\`${field}\` AS \`${table}.${field}\``);
    }
  }
  return fields.join(", ");
}

// ==================== API 接口 ====================

// 1. 测试接口
app.get("/api/test", (req, res) => {
  res.json({
    success: true,
    message: "后端连接成功！",
    time: new Date().toLocaleString(),
  });
});

// 2. 获取所有表和字段信息
app.get("/api/tables", async (req, res) => {
  let connection;
  try {
    console.log("📊 开始获取数据库表信息...");
    connection = await promisePool.getConnection();

    // 获取所有表名
    const [tables] = await connection.query(
      `
      SELECT TABLE_NAME 
      FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_SCHEMA = ?
    `,
      [dbConfig.database]
    );

    // 为每个表获取字段
    const tableList = [];
    for (const table of tables) {
      const tableName = table.TABLE_NAME;

      const [columns] = await connection.query(
        `
        SELECT COLUMN_NAME, DATA_TYPE 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        ORDER BY ORDINAL_POSITION
      `,
        [dbConfig.database, tableName]
      );

      tableList.push({
        name: tableName,
        icon: getTableIcon(tableName),
        fields: columns.map((col) => col.COLUMN_NAME),
        fieldTypes: columns.reduce((acc, col) => {
          acc[col.COLUMN_NAME] = col.DATA_TYPE;
          return acc;
        }, {}),
      });
    }

    console.log(`✅ 成功获取 ${tableList.length} 个表的信息`);

    res.json({
      success: true,
      tables: tableList,
    });
  } catch (error) {
    console.error("❌ 获取表信息失败:", error.message);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  } finally {
    if (connection) connection.release();
  }
});

// 3. 执行查询
app.post("/api/query", async (req, res) => {
  let connection;
  const startTime = Date.now();

  try {
    const { tables, relations, mode, conditions, query } = req.body;

    console.log("🔍 收到查询请求:");
    console.log("  - 表:", tables);
    console.log("  - 模式:", mode);
    console.log("  - 关联:", relations);

    // 验证参数
    if (!tables || tables.length === 0) {
      throw new Error("请至少选择一张表");
    }

    connection = await promisePool.getConnection();

    // 获取所有选中表的字段
    const availableFields = {};
    for (const tableName of tables) {
      const [columns] = await connection.query(
        `
        SELECT COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        ORDER BY ORDINAL_POSITION
      `,
        [dbConfig.database, tableName]
      );

      availableFields[tableName] = columns.map((col) => col.COLUMN_NAME);
    }

    // 构建SQL查询
    let sql = "";

    // 构建SELECT字段
    const selectFields = buildSelectFields(tables, availableFields);

    // 开始构建SQL
    sql = `SELECT ${selectFields} FROM \`${tables[0]}\``;

    // 添加JOIN（如果有多张表）
    if (tables.length > 1 && relations && relations.length > 0) {
      sql += " " + buildJoinClause(relations);
    }

    // 添加WHERE条件
    if (mode === "simple" && conditions && conditions.length > 0) {
      const whereClause = buildSimpleWhere(conditions);
      if (whereClause) {
        sql += " " + whereClause;
      }
    } else if (mode === "advanced" && query && query.trim()) {
      // 高级模式：直接使用用户输入的条件
      // 注意：这里需要对用户输入进行安全处理，防止SQL注入
      sql += ` WHERE ${query}`;
    }

    console.log("📝 生成的SQL:", sql);

    // 执行查询
    const [results] = await connection.query(sql);

    // 转换结果格式（去除表名前缀，使其更易读）
    const formattedResults = results.map((row) => {
      const newRow = {};
      for (const key in row) {
        // 保持原有的"表名.字段名"格式，便于识别
        newRow[key] = row[key];
      }
      return newRow;
    });

    const endTime = Date.now();
    const queryTime = endTime - startTime;

    console.log(
      `✅ 查询成功，返回 ${formattedResults.length} 条记录，耗时 ${queryTime}ms`
    );

    res.json({
      success: true,
      results: formattedResults,
      count: formattedResults.length,
      queryTime: `${queryTime}ms`,
      sql: sql, // 返回SQL供调试使用
    });
  } catch (error) {
    console.error("❌ 查询失败:", error.message);
    res.status(500).json({
      success: false,
      message: error.message,
      error: error.toString(),
    });
  } finally {
    if (connection) await connection.release();
  }
});
// 用于检查中文字体文件是否存在，找不到就抛错

function assertCjkFont() {
  if (!fs.existsSync(CJK_FONT_PATH)) {
    console.error("\n❌ [PDF Export Error] 中文字体文件未找到！");
    console.error("请确保存在文件：", CJK_FONT_PATH);
    console.error("建议放置思源黑体 / Noto Sans SC 等支持中文的字体。\n");
    throw new Error(`CJK font not found: ${CJK_FONT_PATH}`);
  }
}

app.post("/api/export", async (req, res, next) => {
  console.log("req " + req);
  const { sql, params = [], format = "csv" } = req.body || {};
  if (!sql || typeof sql !== "string") {
    return res.status(400).json({ message: "sql is required" });
  }

  // Safety rails
  const LOWER = sql.trim().toLowerCase();
  if (
    LOWER.startsWith("insert") ||
    LOWER.startsWith("update") ||
    LOWER.startsWith("delete") ||
    LOWER.startsWith("alter") ||
    LOWER.startsWith("drop") ||
    LOWER.startsWith("truncate")
  ) {
    return res
      .status(400)
      .json({ message: "Only SELECT queries are allowed for export" });
  }

  try {
    if (format === "csv") {
      await streamCsv(sql, params, res);
    } else if (format === "pdf") {
      await streamPdfSmall(sql, params, res);
    } else if (format === "excel") {
      // 依赖：npm i exceljs
      const ExcelJS = require("exceljs");

      // 统一 ASCII 文件名
      const baseName =
        req.body &&
        typeof req.body.filename === "string" &&
        req.body.filename.trim()
          ? req.body.filename.trim()
          : "export";
      const safeBase = baseName.replace(/[^\w\-]+/g, "_");

      // 1) 准备数据源：优先使用当前作用域已有的 rows/fields；否则回退到执行 SQL；再不行从 rows 推导列
      let rowsData =
        typeof rows !== "undefined"
          ? rows
          : res.locals && Array.isArray(res.locals.rows)
          ? res.locals.rows
          : [];
      let fieldsData =
        typeof fields !== "undefined"
          ? fields
          : res.locals && Array.isArray(res.locals.fields)
          ? res.locals.fields
          : [];

      if ((!rowsData || rowsData.length === 0) && req.body && req.body.sql) {
        try {
          const [qRows, qFields] = await promisePool.query(
            req.body.sql,
            req.body.params || []
          );
          rowsData = qRows;
          fieldsData = qFields;
        } catch (e) {
          console.error("[xlsx] query failed:", e);
          return res
            .status(500)
            .json({ message: "failed to query for xlsx export" });
        }
      }

      if (!rowsData || rowsData.length === 0) {
        return res.status(200).json({ message: "no data to export (xlsx)" });
      }

      // 2) 列定义：优先用 fields；没有 fields 就用 rows 的 key 推导
      const columnKeys =
        fieldsData && fieldsData.length
          ? fieldsData.map((f) => f.name)
          : Object.keys(rowsData[0] || {});

      const columns = columnKeys.map((k) => ({ header: k, key: k }));

      // 3) 设置响应头（流式写回）
      res.setHeader(
        "Content-Type",
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
      );
      res.setHeader(
        "Content-Disposition",
        `attachment; filename="${safeBase}.xlsx"`
      );

      // 4) 流式写入，避免内存爆
      const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({
        stream: res,
        useStyles: true,
        useSharedStrings: true,
      });

      const worksheet = workbook.addWorksheet("Export", {
        views: [{ state: "frozen", ySplit: 1 }], // 冻结表头
        properties: { defaultRowHeight: 18 },
      });

      worksheet.columns = columns;

      // 5) 自动列宽（采样，轻量）
      const MIN_W = 8; // Excel 列宽单位（大致按字符宽）
      const MAX_W = 50;
      const sampleRows = rowsData.slice(0, Math.min(1000, rowsData.length));
      const widths = columns.map((c) => Math.max((c.header || "").length, 0));

      for (const r of sampleRows) {
        columns.forEach((c, i) => {
          const v = r[c.key] == null ? "" : String(r[c.key]);
          const len = v.length; // 简单长度估算（够用且快）
          if (len > widths[i]) widths[i] = len;
        });
      }

      worksheet.columns.forEach((col, i) => {
        col.width = Math.max(MIN_W, Math.min(MAX_W, (widths[i] || 0) + 2));
        col.alignment = { vertical: "top", wrapText: true };
      });

      // 6) 表头样式（可选）
      const headerRow = worksheet.getRow(1);
      headerRow.font = { bold: true };
      headerRow.alignment = { vertical: "middle" };
      headerRow.commit();

      // 7) 写数据（流式逐行）
      for (const r of rowsData) {
        const rowObj = {};
        for (const c of columns) rowObj[c.key] = r[c.key];
        worksheet.addRow(rowObj).commit();
      }

      // 8) 如果你用了 PDF_MAX_ROWS / PDF_MAX_COLS 做截断，也给个注释行（可选）
      if (
        typeof PDF_MAX_ROWS !== "undefined" &&
        typeof PDF_MAX_COLS !== "undefined"
      ) {
        if (
          rowsData.length >= PDF_MAX_ROWS ||
          columnKeys.length >= PDF_MAX_COLS
        ) {
          const note = worksheet.addRow({
            [columns[0]
              .key]: `Note: data may be truncated for preview. Export CSV for full dataset.`,
          });
          note.font = { italic: true, color: { argb: "FF666666" } };
          note.commit();
        }
      }

      await worksheet.commit();
      await workbook.commit();
      return;
    } else {
      return res.status(400).json({ message: "Unknown format" });
    }
  } catch (e) {
    next(e);
  }
});

async function streamCsv(sql, params, res) {
  res.setHeader("Content-Type", "text/csv; charset=utf-8");
  res.setHeader("Content-Disposition", 'attachment; filename="export.csv"');
  // BOM for Excel
  res.write("\uFEFF");

  // We need headers: do a small probe query to get field names
  const probe = pool.query(sql, params);
  let headers = null;
  probe.on("fields", (fields) => {
    headers = fields.map((f) => f.name);
    res.write(headers.join(",") + "\n");
  });
  const stream = probe.stream({ highWaterMark: 100 });

  stream.on("data", (row) => {
    // row is an object with keys = headers
    const line = (headers || Object.keys(row))
      .map((key) => csvEscape(sanitizeVal(row[key])))
      .join(",");
    res.write(line + "\n");
  });
  stream.on("error", (err) => {
    console.error("[CSV stream error]", err);
    if (!res.headersSent) res.status(500);
    res.end();
  });
  stream.on("end", () => {
    res.end();
  });
}

// PDF is heavy; restrict rows/columns to keep it responsive
const PDF_MAX_ROWS = 1500; // tune as needed
const PDF_MAX_COLS = 25;

// async function streamPdfSmall(sql, params, res) {
//   // 估算每列宽度（采样少量行，避免全量测量造成卡顿）
//   function computeColWidths(doc, cols, data, innerWidth, padX = 6) {
//     doc.fontSize(9);
//     const sample = data.slice(0, Math.min(200, data.length));
//     const widths = cols.map((h) => doc.widthOfString(String(h)));
//     for (const r of sample) {
//       cols.forEach((c, i) => {
//         const v = r[c] == null ? "" : String(r[c]);
//         const w = doc.widthOfString(v);
//         if (w > widths[i]) widths[i] = w;
//       });
//     }
//     // padding + 设定最小/最大约束
//     const MIN_W = 60,
//       MAX_W = Math.max(120, innerWidth * 0.45);
//     let arr = widths.map((w) => Math.min(MAX_W, Math.max(MIN_W, w + padX * 2)));
//     // 如总宽超出，等比收缩
//     const sum = arr.reduce((a, b) => a + b, 0);
//     if (sum > innerWidth) {
//       const ratio = innerWidth / sum;
//       arr = arr.map((w) => Math.max(MIN_W, Math.floor(w * ratio)));
//     }
//     return arr;
//   }

//   // 把列切成多个“窗”（可设置冻结列数），每个窗的总宽不超过页面可用宽度
//   function buildColumnWindows(colWidths, maxWidth, frozenCount = 1) {
//     const n = colWidths.length;
//     const idxs = [...Array(n).keys()];
//     const frozen = idxs.slice(0, Math.min(frozenCount, n));
//     const dynIdxs = idxs.slice(frozen.length);

//     const frozenWidth = frozen.reduce((s, i) => s + colWidths[i], 0);
//     const windows = [];

//     // 如果冻结列本身已占满，降低冻结列数量兜底
//     let fCount = frozen.length;
//     while (fCount > 0 && frozenWidth > maxWidth * 0.75) {
//       fCount--;
//     }
//     const frozenFinal = idxs.slice(0, fCount);
//     const frozenWidthFinal = frozenFinal.reduce((s, i) => s + colWidths[i], 0);

//     let cur = [];
//     let curWidth = frozenWidthFinal;

//     const pushWindow = () => {
//       if (frozenFinal.length + cur.length > 0) {
//         windows.push([...frozenFinal, ...cur]);
//       }
//       cur = [];
//       curWidth = frozenWidthFinal;
//     };

//     for (const i of idxs.slice(fCount)) {
//       const w = colWidths[i];
//       if (curWidth + w <= maxWidth || cur.length === 0) {
//         cur.push(i);
//         curWidth += w;
//       } else {
//         pushWindow();
//         cur.push(i);
//         curWidth += w;
//       }
//     }
//     pushWindow();
//     return windows;
//   }

//   // Run the full query (small-ish) — for large exports prefer CSV/Excel
//   const [rows, fields] = await promisePool.query(sql, params);
//   const cols = fields.map((f) => f.name).slice(0, PDF_MAX_COLS);
//   const data = rows.slice(0, PDF_MAX_ROWS);

//   res.setHeader("Content-Type", "application/pdf");
//   res.setHeader("Content-Disposition", 'attachment; filename="export.pdf"');
//   const pageSize = cols.length > 14 ? "A3" : "A4";

//   const doc = new PDFDocument({
//     size: pageSize,
//     margin: 36,
//     layout: cols.length > 6 ? "landscape" : "portrait",
//   });
//   doc.pipe(res);
//   doc.registerFont("CJK", CJK_FONT_PATH);
//   doc.font("CJK");

//   doc.fontSize(14).text("Export", { align: "left" });
//   doc.moveDown(0.5);

//   // simple table layout
//   // const pageWidth =
//   //   doc.page.width - doc.page.margins.left - doc.page.margins.right;
//   // const colWidth = Math.max(60, Math.floor(pageWidth / cols.length));

//   // // const drawRow = (arr, isHeader = false) => {
//   // //   const yStart = doc.y;
//   // //   arr.forEach((cell, i) => {
//   // //     const txt = String(cell ?? "");
//   // //     const x = doc.page.margins.left + i * colWidth;
//   // //     doc
//   // //       .font(isHeader ? "Helvetica-Bold" : "Helvetica")
//   // //       .fontSize(isHeader ? 10 : 9);
//   // //     doc.text(txt, x + 2, yStart, { width: colWidth - 4, continued: false });
//   // //   });
//   // //   doc.moveDown(0.5);
//   // // };
//   // const drawRow = (arr, isHeader = false) => {
//   //   const padX = 6; // 左右内边距
//   //   const padY = 6; // 上下内边距
//   //   const yStart = doc.y;

//   //   // 计算每个单元格文本高度，取最大值作为行高
//   //   const heights = arr.map((cell) =>
//   //     doc.heightOfString(String(cell ?? ""), { width: colWidth - padX * 2 })
//   //   );
//   //   const rowH = Math.max(...heights) + padY * 2;

//   //   // 背景（表头加浅灰）
//   //   if (isHeader) {
//   //     doc
//   //       .save()
//   //       .fillColor("#F2F2F2")
//   //       .rect(doc.page.margins.left, yStart, colWidth * cols.length, rowH)
//   //       .fill()
//   //       .restore();
//   //   }

//   //   // 画每个单元格文字
//   //   arr.forEach((cell, i) => {
//   //     const txt = String(cell ?? "");
//   //     const x = doc.page.margins.left + i * colWidth;

//   //     doc
//   //       .font(isHeader ? "CJK" : "CJK")
//   //       .fontSize(isHeader ? 10 : 9)
//   //       .fillColor("#111");
//   //     doc.text(txt, x + padX, yStart + padY, {
//   //       width: colWidth - padX * 2,
//   //       align: "left",
//   //     });

//   //     // 竖线
//   //     doc
//   //       .save()
//   //       .strokeColor("#E5E5E5")
//   //       .moveTo(x + colWidth, yStart)
//   //       .lineTo(x + colWidth, yStart + rowH)
//   //       .stroke()
//   //       .restore();
//   //   });

//   //   // 底部横线
//   //   doc
//   //     .save()
//   //     .strokeColor("#E5E5E5")
//   //     .moveTo(doc.page.margins.left, yStart + rowH)
//   //     .lineTo(doc.page.margins.left + colWidth * cols.length, yStart + rowH)
//   //     .stroke()
//   //     .restore();

//   //   // 将当前 Y 推进到本行之后
//   //   doc.y = yStart + rowH;
//   //   return rowH;
//   // 计算可用宽度
//   const innerWidth =
//     doc.page.width - doc.page.margins.left - doc.page.margins.right;

//   // 1) 按内容估算每列实际需要的宽度（已做采样与限宽）
//   const colWidths = computeColWidths(doc, cols, data, innerWidth);

//   // 2) 把所有列切成多个“窗”，每个窗的总宽不超过页面可用宽度
//   //    第一个参数：每列宽度数组
//   //    第二个参数：最大可用宽度
//   //    第三个参数：冻结列的数量（建议保留 1 列作为行锚点；如需两列改为 2）
//   const windows = buildColumnWindows(colWidths, innerWidth, 1);

//   // 注意：真正绘制发生在“改动 2”的循环里。
//   // 下面定义一个“当前窗专用”的绘制函数，在循环里按窗重建：
//   let drawRowWin = null;
// }

// // drawRow(cols, true);
// // doc
// //   .moveTo(doc.page.margins.left, doc.y)
// //   .lineTo(doc.page.width - doc.page.margins.right, doc.y)
// //   .stroke();
// // doc.moveDown(0.25);

// // for (const r of data) {
// //   const values = cols.map((c) => sanitizeVal(r[c]));

// //   // 预估该行高度（与 drawRow 同口径）
// //   const estH =
// //     Math.max(
// //       ...values.map((cell) =>
// //         doc.heightOfString(String(cell ?? ""), { width: colWidth - 12 })
// //       )
// //     ) + 12; // padY*2 = 12

// //   // 如果放不下，新起一页并重绘表头
// //   const bottomY = doc.page.height - doc.page.margins.bottom;
// //   if (doc.y + estH > bottomY) {
// //     doc.addPage();
// //     // 重画表头
// //     drawRow(cols, true);
// //   }

// //   // 画数据行
// //   drawRow(values, false);
// // }
// // 3) 按“列窗”循环绘制（横向分页）
// for (let wi = 0; wi < windows.length; wi++) {
//   const colIdxs = windows[wi]; // 本窗需要显示的列索引
//   const headerArr = colIdxs.map((i) => cols[i]); // 本窗的表头
//   const colWidthArr = colIdxs.map((i) => colWidths[i]);
//   const totalW = colWidthArr.reduce((a, b) => a + b, 0);

//   // 不是第一窗就换新页
//   if (wi > 0) {
//     doc.addPage();
//   }

//   // 页眉（标题）
//   doc.fontSize(14).text("Export", { align: "left" });
//   doc.moveDown(0.5);

//   // 为“当前窗”定义专用的 drawRowWin（用 colWidthArr 计算高度与定位）
//   drawRowWin = (arr, isHeader = false, zebraIndex = 0) => {
//     const padX = 6,
//       padY = 6;
//     const yStart = doc.y;

//     // 计算行高（与绘制口径一致）
//     const heights = arr.map((cell, i) =>
//       doc.heightOfString(String(cell ?? ""), {
//         width: colWidthArr[i] - padX * 2,
//       })
//     );
//     const rowH = Math.max(...heights) + padY * 2;

//     // 表头或斑马纹背景
//     if (isHeader) {
//       doc
//         .save()
//         .fillColor("#F2F2F2")
//         .rect(doc.page.margins.left, yStart, totalW, rowH)
//         .fill()
//         .restore();
//     } else if (zebraIndex % 2 === 1) {
//       doc
//         .save()
//         .fillColor("#FBFBFD")
//         .rect(doc.page.margins.left, yStart, totalW, rowH)
//         .fill()
//         .restore();
//     }

//     // 单元格文字 + 栅格线
//     let x = doc.page.margins.left;
//     arr.forEach((cell, i) => {
//       const txt = String(cell ?? "");
//       doc
//         .font("CJK")
//         .fontSize(isHeader ? 10 : 9)
//         .fillColor("#111")
//         .text(txt, x + padX, yStart + padY, {
//           width: colWidthArr[i] - padX * 2,
//           align: "left",
//         });

//       // 竖线
//       doc
//         .save()
//         .strokeColor("#E5E5E5")
//         .moveTo(x + colWidthArr[i], yStart)
//         .lineTo(x + colWidthArr[i], yStart + rowH)
//         .stroke()
//         .restore();

//       x += colWidthArr[i];
//     });

//     // 底线
//     doc
//       .save()
//       .strokeColor("#E5E5E5")
//       .moveTo(doc.page.margins.left, yStart + rowH)
//       .lineTo(doc.page.margins.left + totalW, yStart + rowH)
//       .stroke()
//       .restore();

//     doc.y = yStart + rowH;
//     return rowH;
//   };

//   // 画本窗表头
//   drawRowWin(headerArr, true);
//   doc
//     .moveTo(doc.page.margins.left, doc.y)
//     .lineTo(doc.page.width - doc.page.margins.right, doc.y)
//     .stroke();
//   doc.moveDown(0.25);

//   // 遍历数据行（含纵向分页估高）
//   let rowIdx = 0;
//   for (const r of data) {
//     const values = colIdxs.map((i) => sanitizeVal(r[cols[i]]));

//     // 估高（与 drawRowWin 同口径），不足则分页并重画表头
//     const estH =
//       Math.max(
//         ...values.map((cell, i) =>
//           doc.heightOfString(String(cell ?? ""), { width: colWidthArr[i] - 12 })
//         )
//       ) + 12; // padY*2
//     const bottomY = doc.page.height - doc.page.margins.bottom;
//     if (doc.y + estH > bottomY) {
//       doc.addPage();
//       drawRowWin(headerArr, true);
//       doc
//         .moveTo(doc.page.margins.left, doc.y)
//         .lineTo(doc.page.width - doc.page.margins.right, doc.y)
//         .stroke();
//       doc.moveDown(0.25);
//     }

//     drawRowWin(values, false, rowIdx);
//     rowIdx++;
//   }
// }

// if (rows.length > PDF_MAX_ROWS || fields.length > PDF_MAX_COLS) {
//   doc.moveDown(0.5);
//   doc
//     .fontSize(9)
//     .fillColor("gray")
//     .text(
//       `Note: Truncated to ${Math.min(
//         rows.length,
//         PDF_MAX_ROWS
//       )} rows and ${Math.min(
//         fields.length,
//         PDF_MAX_COLS
//       )} columns. Use CSV for full export.`
//     );
// }
// // 关键保护：如果 PDF 发生错误或客户端中断，确保结束文档/响应
// doc.on("error", (err) => {
//   console.error("[PDF stream error]", err);
//   if (!res.headersSent) res.statusCode = 500;
//   if (!res.writableEnded) res.end();
// });

// res.on("close", () => {
//   // 避免在 res 已关闭时继续写
//   try {
//     if (!doc._ended) doc.end();
//   } catch {}
// });
// doc.end();

// // ---------- Errors ----------
// app.use((err, req, res, next) => {
//   console.error(err);
//   if (res.headersSent) return next(err);
//   res
//     .status(500)
//     .json({ message: "Internal error", detail: String(err?.message || err) });
// });

// 直接替换本函数
async function streamPdfSmall(sql, params, res) {
  // 1) 拉取数据（小批量，PDF 只做预览用途）
  const [rows, fields] = await promisePool.query(sql, params);
  const cols = fields.map((f) => f.name).slice(0, PDF_MAX_COLS);
  const data = rows.slice(0, PDF_MAX_ROWS);

  // 无数据兜底：回一页“无数据”的 PDF
  if (cols.length === 0 || data.length === 0) {
    res.setHeader("Content-Type", "application/pdf");
    res.setHeader("Content-Disposition", 'attachment; filename="export.pdf"');
    const docEmpty = new PDFDocument({ size: "A4", margin: 36 });
    docEmpty.pipe(res);
    try {
      docEmpty.registerFont("CJK", CJK_FONT_PATH);
      docEmpty.font("CJK");
    } catch {}
    docEmpty.fontSize(14).text("Export", { align: "left" }).moveDown(0.5);
    docEmpty.fontSize(10).fillColor("gray").text("No data to export.");
    docEmpty.end();
    return;
  }

  // 2) 输出头
  res.setHeader("Content-Type", "application/pdf");
  res.setHeader("Content-Disposition", 'attachment; filename="export.pdf"');

  // 3) 页面尺寸/方向（列很多时用 A3，更宽）
  const pageSize = cols.length > 14 ? "A3" : "A4";
  const layout = cols.length > 6 ? "landscape" : "portrait";
  const MARGIN = 36;

  const doc = new PDFDocument({ size: pageSize, margin: MARGIN, layout });
  doc.pipe(res);

  // 中文字体（解决乱码）
  doc.registerFont("CJK", CJK_FONT_PATH);
  doc.font("CJK");

  // 标题
  doc.fontSize(14).text("Export", { align: "left" });
  doc.moveDown(0.5);

  // ---------- 工具函数（函数声明会提升，但放前面更直观） ----------
  function estimateColWidths(doc, cols, data, innerWidth, padX = 6) {
    doc.font("CJK").fontSize(9);
    const sample = data.slice(0, Math.min(200, data.length)); // 采样避免全量测量
    const widths = cols.map((h) => doc.widthOfString(String(h)));
    for (const r of sample) {
      cols.forEach((c, i) => {
        const v = r[c] == null ? "" : String(r[c]);
        const w = doc.widthOfString(v);
        if (w > widths[i]) widths[i] = w;
      });
    }
    const MIN_W = 60;
    const MAX_W = Math.max(120, innerWidth * 0.45);
    let arr = widths.map((w) => Math.min(MAX_W, Math.max(MIN_W, w + padX * 2)));
    const sum = arr.reduce((a, b) => a + b, 0);
    if (sum > innerWidth) {
      const ratio = innerWidth / sum;
      arr = arr.map((w) => Math.max(MIN_W, Math.floor(w * ratio)));
    }
    return arr;
  }

  function buildColumnWindows(colWidths, maxWidth, frozenCount = 1) {
    const n = colWidths.length;
    const idxs = [...Array(n).keys()];
    let fCount = Math.min(frozenCount, n);

    // 冻结列太宽就降级
    while (fCount > 0) {
      const frozenWidthTry = idxs
        .slice(0, fCount)
        .reduce((s, i) => s + colWidths[i], 0);
      if (frozenWidthTry <= maxWidth * 0.75) break;
      fCount--;
    }

    const frozen = idxs.slice(0, fCount);
    const dynamic = idxs.slice(fCount);
    const frozenWidth = frozen.reduce((s, i) => s + colWidths[i], 0);

    const windows = [];
    let cur = [];
    let curWidth = frozenWidth;

    const pushWindow = () => {
      if (frozen.length + cur.length > 0) windows.push([...frozen, ...cur]);
      cur = [];
      curWidth = frozenWidth;
    };

    for (const i of dynamic) {
      const w = colWidths[i];
      if (cur.length === 0 || curWidth + w <= maxWidth) {
        cur.push(i);
        curWidth += w;
      } else {
        pushWindow();
        cur.push(i);
        curWidth += w;
      }
    }
    pushWindow();
    return windows;
  }
  // ---------- /工具函数 ----------

  // 4) 计算可用宽度 / 列宽 / 列窗（注意顺序！）
  const innerWidth =
    doc.page.width - doc.page.margins.left - doc.page.margins.right;
  const colWidths = estimateColWidths(doc, cols, data, innerWidth);
  const windows = buildColumnWindows(colWidths, innerWidth, 1); // 冻结首列；要两列改为 2

  // 5) 横向分页：按“列窗”循环绘制
  for (let wi = 0; wi < windows.length; wi++) {
    const colIdxs = windows[wi];
    const headerArr = colIdxs.map((i) => cols[i]);
    const colWidthArr = colIdxs.map((i) => colWidths[i]);
    const totalW = colWidthArr.reduce((a, b) => a + b, 0);

    if (wi > 0) {
      doc.addPage({ size: pageSize, margin: MARGIN, layout });
      doc.fontSize(14).text("Export", { align: "left" });
      doc.moveDown(0.5);
    }

    // 当前窗的画行函数
    const drawRowWin = (arr, isHeader = false, zebraIndex = 0) => {
      const padX = 6,
        padY = 6;
      const yStart = doc.y;

      const heights = arr.map((cell, i) =>
        doc.heightOfString(String(cell ?? ""), {
          width: colWidthArr[i] - padX * 2,
        })
      );
      const rowH = Math.max(...heights) + padY * 2;

      if (isHeader) {
        doc
          .save()
          .fillColor("#F2F2F2")
          .rect(doc.page.margins.left, yStart, totalW, rowH)
          .fill()
          .restore();
      } else if (zebraIndex % 2 === 1) {
        doc
          .save()
          .fillColor("#FBFBFD")
          .rect(doc.page.margins.left, yStart, totalW, rowH)
          .fill()
          .restore();
      }

      let x = doc.page.margins.left;
      arr.forEach((cell, i) => {
        const txt = String(cell ?? "");
        doc
          .font("CJK")
          .fontSize(isHeader ? 10 : 9)
          .fillColor("#111")
          .text(txt, x + padX, yStart + padY, {
            width: colWidthArr[i] - padX * 2,
            align: "left",
          });

        // 竖线
        doc
          .save()
          .strokeColor("#E5E5E5")
          .moveTo(x + colWidthArr[i], yStart)
          .lineTo(x + colWidthArr[i], yStart + rowH)
          .stroke()
          .restore();

        x += colWidthArr[i];
      });

      // 行底线
      doc
        .save()
        .strokeColor("#E5E5E5")
        .moveTo(doc.page.margins.left, yStart + rowH)
        .lineTo(doc.page.margins.left + totalW, yStart + rowH)
        .stroke()
        .restore();

      doc.y = yStart + rowH;
      return rowH;
    };

    // 表头
    drawRowWin(headerArr, true);
    doc
      .moveTo(doc.page.margins.left, doc.y)
      .lineTo(doc.page.width - doc.page.margins.right, doc.y)
      .stroke();
    doc.moveDown(0.25);

    // 数据行（带纵向分页）
    let rowIdx = 0;
    for (const r of data) {
      const values = colIdxs.map((i) => sanitizeVal(r[cols[i]]));

      const estH =
        Math.max(
          ...values.map((cell, i) =>
            doc.heightOfString(String(cell ?? ""), {
              width: colWidthArr[i] - 12,
            })
          )
        ) + 12; // padY*2

      const bottomY = doc.page.height - doc.page.margins.bottom;
      if (doc.y + estH > bottomY) {
        doc.addPage({ size: pageSize, margin: MARGIN, layout });
        // 新页表头
        drawRowWin(headerArr, true);
        doc
          .moveTo(doc.page.margins.left, doc.y)
          .lineTo(doc.page.width - doc.page.margins.right, doc.y)
          .stroke();
        doc.moveDown(0.25);
      }

      drawRowWin(values, false, rowIdx);
      rowIdx++;
    }
  }

  // 截断提示（保留你原有的导出上限提示）
  if (rows.length > PDF_MAX_ROWS || fields.length > PDF_MAX_COLS) {
    doc.moveDown(0.5);
    doc
      .font("CJK")
      .fontSize(9)
      .fillColor("gray")
      .text(
        `Note: Truncated to ${Math.min(
          rows.length,
          PDF_MAX_ROWS
        )} rows and ${Math.min(fields.length, PDF_MAX_COLS)} columns. ` +
          `Export CSV/Excel for the full dataset.`
      );
  }

  // 错误/关闭处理
  doc.on("error", (err) => {
    console.error("[PDF stream error]", err);
    if (!res.headersSent) res.statusCode = 500;
    if (!res.writableEnded) res.end();
  });
  res.on("close", () => {
    try {
      if (!doc._ended) doc.end();
    } catch {}
  });

  // 完成
  doc.end();
}

app.listen(PORT, "0.0.0.0", () => {
  console.log(`Server listening on http://127.0.0.1:${PORT}`);
});
// 5. 模板管理接口（使用内存存储，实际项目中应该存到数据库）
let templates = [
  {
    id: 1,
    name: "示例查询模板",
    mode: "simple",
    tables: [],
    relations: [],
    conditions: [],
  },
];

// 获取所有模板
app.get("/api/templates", (req, res) => {
  console.log("📋 获取模板列表");
  res.json({
    success: true,
    templates: templates,
  });
});

// 保存模板
app.post("/api/templates", (req, res) => {
  try {
    const template = req.body;
    template.id = Date.now(); // 简单的ID生成
    templates.push(template);

    console.log(`✅ 保存模板: ${template.name}`);

    res.json({
      success: true,
      template: template,
      message: "模板保存成功",
    });
  } catch (error) {
    console.error("❌ 保存模板失败:", error.message);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
});

// 删除模板
app.delete("/api/templates/:id", (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const index = templates.findIndex((t) => t.id === id);

    if (index === -1) {
      throw new Error("模板不存在");
    }

    templates.splice(index, 1);

    console.log(`✅ 删除模板: ID ${id}`);

    res.json({
      success: true,
      message: "模板删除成功",
    });
  } catch (error) {
    console.error("❌ 删除模板失败:", error.message);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
});

// 6. 获取表预览数据（前10行）
app.get("/api/table-preview/:tableName", async (req, res) => {
  let connection;
  try {
    const tableName = req.params.tableName;

    console.log(`👀 预览表: ${tableName}`);

    connection = await promisePool.getConnection();

    const [rows] = await connection.query(
      `SELECT * FROM \`${tableName}\` LIMIT 10`
    );

    res.json({
      success: true,
      data: rows,
      count: rows.length,
    });

    console.log(`✅ 返回 ${rows.length} 条预览数据`);
  } catch (error) {
    console.error("❌ 预览失败:", error.message);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  } finally {
    if (connection) await connection.release();
  }
});

// ==================== 启动服务 ====================

app.listen(PORT, () => {
  console.log(`
╔════════════════════════════════════════════════════╗
║                                                    ║
║   🚀 跨表查询系统后端服务已启动！                  ║
║                                                    ║
║   📍 服务地址: http://localhost:${PORT}              ║
║   🗄️  数据库: ${dbConfig.database}                    ║
║                                                    ║
║   📡 可用接口:                                      ║
║   ├─ GET  /api/test                               ║
║   ├─ GET  /api/tables                             ║
║   ├─ POST /api/query                              ║
║   ├─ POST /api/export                             ║
║   ├─ GET  /api/templates                          ║
║   ├─ POST /api/templates                          ║
║   ├─ DELETE /api/templates/:id                    ║
║   └─ GET  /api/table-preview/:tableName           ║
║                                                    ║
╚════════════════════════════════════════════════════╝
  `);
});
